package net.skyedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.skyedu.bean.BasGrade;
import net.skyedu.bean.SysCourse;
import net.skyedu.bean.SysOutline;

public class DaoSupport extends DAO{
	
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	/**
	 * 执行SQL
	 * @param sql
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private void exec(StringBuffer sql) throws ClassNotFoundException,
	SQLException {
		conn=this.getConn();
		pstmt=conn.prepareStatement(sql.toString());
		rs=pstmt.executeQuery();
	}
	/**
	 * 添加课程
	 * @param 
	 * @return
	 */
	public int savaCourse(SysCourse cou){
		StringBuffer sql = new StringBuffer("insert into sys_course(cou_year,cou_term,cou_subject," +
				"cou_grade,cou_claType,cou_claLevel,cou_beginTime,cou_endTime," +
				"cou_classNumber,cou_classHour,cou_perunitFee,cou_predictNum,cou_bookFee,cou_bookVersion," +
				"cou_classTime,cou_createTime) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,GETDATE())");
		String [] params = new String[] {cou.getYear(),cou.getTerName(),cou.getSubName(),cou.getGraName(),
				cou.getClaType(),cou.getClaLevel(),cou.getBeginTime(),cou.getEndTime(),
				cou.getClaNumber()+"",cou.getClaHour()+"",cou.getPerunitFee()+"",cou.getPredictNum()+"",
				cou.getBookFee()+"",cou.getBookVersion(),cou.getClassTime()+""};
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * 修改课程
	 * @param cou
	 * @return
	 */
	public int upCourse(SysCourse cou){
		StringBuffer sql = new StringBuffer("update sys_course set cou_year=?,cou_term=?,cou_subject=?," +
				"cou_grade=?,cou_claType=?,cou_claLevel=?,cou_beginTime=?,cou_endTime=?," +
				"cou_classNumber=?,cou_classHour=?,cou_perunitFee=?,cou_predictNum=?,cou_bookFee=?,cou_bookVersion=?," +
				"cou_classTime=? where cou_id=?");
		String [] params = new String[] {cou.getYear(),cou.getTerName(),cou.getSubName(),cou.getGraName(),
				cou.getClaType(),cou.getClaLevel(),cou.getBeginTime(),cou.getEndTime(),
				cou.getClaNumber()+"",cou.getClaHour()+"",cou.getPerunitFee()+"",cou.getPredictNum()+"",
				cou.getBookFee()+"",cou.getBookVersion(),cou.getClassTime()+"",cou.getCouId()+""};
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * 添加课程大纲
	 * @param out
	 * @return
	 */
	public int savaOutline(SysOutline out){
		StringBuffer sql=new StringBuffer("insert into sys_outline(out_cou_id,out_claContent,out_teaAims," +
				"out_knowledge,out_difficulty,out_count) " +
				"values(?,?,?,?,?,?)");
		String [] params=new String[] {out.getOutCouId()+"",out.getClaContent(),out.getTeaAims(),out.getKnowledge()
				,out.getDifficulty()+"",out.getClaCount()+""};
		return this.executeSQL(sql.toString(), params);
	}
	public int updateOutline(SysOutline out)
	{
		StringBuffer sql = new StringBuffer("update sys_outline set out_claContent=?,out_teaAims=?,out_knowledge=?," +
				"out_difficulty=? where out_cou_id=? and out_count=?");
		System.out.println("update sys_outline set out_claContent="+out.getClaContent()+",out_teaAims="+out.getTeaAims()+",out_knowledge="+out.getKnowledge()+"," +
				"out_difficulty="+Integer.toString(out.getDifficulty())+" where out_cou_id="+Integer.toString(out.getOutCouId())+" and out_count="+Integer.toString(out.getClaCount()));
		String [] params = new String[] {out.getClaContent(),out.getTeaAims(),out.getKnowledge(),Integer.toString(out.getDifficulty()),Integer.toString(out.getOutCouId()),Integer.toString(out.getClaCount())};
		
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * 删除课程
	 * @param cou
	 * @return
	 */
	public int delCourse(SysCourse cou){
		StringBuffer sql=new StringBuffer("delete from sys_course where cou_id=?");
		String [] params=new String[] {cou.getCouId()+""};
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * 删除课程大纲
	 * @param out
	 * @return
	 */
	public int delOutline(SysOutline out){
		StringBuffer sql=new StringBuffer("delete from sys_outline where out_cou_id=?");
		String [] params=new String[] {out.getOutCouId()+""};
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * 显示课程
	 * @param userId
	 * @return
	 */
	public List<SysCourse> list(int startIndex,int pagesize,int type,String term,String subject,String grade,String level){
		List<SysCourse> list = new ArrayList<SysCourse>();
		try{
			StringBuffer sql = new StringBuffer("select cou_id,cou_year,cou_term,cou_subject," +
					"cou_grade,cou_classNumber,cou_claLevel,cou_claType,cou_bookVersion,cou_bookFee," +
					"cou_perunitFee,cou_predictNum " +
					"from sys_course where 1=1 ");
			if(!term.equals("请选择")){
				sql.append("and cou_year='"+term.split(",")[0]+"' and cou_term='"+term.split(",")[1]+"' ");
			}
			if(!subject.equals("请选择")){
				sql.append("and cou_subject='"+subject+"' ");
			}
			if(!grade.equals("请选择")){
				sql.append("and cou_grade='"+grade+"' ");
			}
			if(!level.equals("请选择")){
				sql.append("and cou_claLevel='"+level+"' ");
			}
				sql.append("order by cou_createTime desc");
				System.out.println(sql);
			exec(sql);
			if(type != 0){
				int i=0;
				while(rs.next()){
					if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
						SysCourse obj = new SysCourse();
						obj.setCouId(rs.getInt("cou_id"));
						obj.setYear(rs.getString("cou_year"));
						obj.setTerName(rs.getString("cou_term"));
						obj.setSubName(rs.getString("cou_subject"));
						obj.setGraName(rs.getString("cou_grade"));
						obj.setClaNumber(rs.getInt("cou_classNumber"));
						obj.setClaLevel(rs.getString("cou_claLevel"));
						obj.setClaType(rs.getString("cou_claType"));
						obj.setBookFee(rs.getDouble("cou_bookFee"));
						obj.setBookVersion(rs.getString("cou_bookVersion"));
						obj.setPerunitFee(rs.getInt("cou_perunitFee"));
						obj.setPredictNum(rs.getInt("cou_predictNum"));
						list.add(obj);
					}
					i++;
				}
			}else{
				while(rs.next()){
					SysCourse obj = new SysCourse();
					obj.setCouId(rs.getInt("cou_id"));
					list.add(obj);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 显示课程大纲
	 * @param couId
	 * @return
	 */
	public List<SysOutline> showOutline(int couId){
		List<SysOutline> list = new ArrayList<SysOutline>();
		try{
			StringBuffer sql = new StringBuffer("select * from sys_outline where out_cou_id="+couId+" order by out_count asc");
			System.out.println(sql);
			exec(sql);
			while(rs.next()){
				SysOutline obj=new SysOutline();
				String temp=rs.getString("out_claContent");
				String a[]=temp.split("\\.");
				a[0]=a[0]+".";
				for(int i=1;i<a.length;i++)
				{
					if(a[i].charAt((a[i].length()-1))>47&&a[i].charAt((a[i].length()-1))<58)
					{
						String befor=a[i].substring(0,a[i].length()-1);
						String last=a[i].substring(a[i].length()-1);//得到数字
						String temp2="<br/>"+last+".";
						a[i]=befor+temp2;
					}
				}
				String mm="";
				for(String b:a)
				{
					mm+=b;
				}
				obj.setClaContent(mm);
				String temp1=rs.getString("out_teaAims");
				String a1[]=temp1.split("\\.");
				a1[0]=a1[0]+".";
				for(int i=1;i<a1.length;i++)
				{
					if(a1[i].charAt((a1[i].length()-1))>47&&a1[i].charAt((a1[i].length()-1))<58)
					{
						String befor=a1[i].substring(0,a1[i].length()-1);
						String last=a1[i].substring(a1[i].length()-1);//得到数字
						String temp2="<br/>"+last+".";
						a1[i]=befor+temp2;
					}
				}
				String mm1="";
				for(String b:a1)
				{
					mm1+=b;
				}
				obj.setTeaAims(mm1);
				String temp2=rs.getString("out_knowledge");
				String a2[]=temp2.split("\\.");
				a2[0]=a2[0]+".";
				for(int i=1;i<a2.length;i++)
				{
					if(a2[i].charAt((a2[i].length()-1))>47&&a2[i].charAt((a2[i].length()-1))<58)
					{
						String befor=a2[i].substring(0,a2[i].length()-1);
						String last=a2[i].substring(a2[i].length()-1);//得到数字
						String temp3="<br/>"+last+".";
						a2[i]=befor+temp3;
					}
				}
				String mm2="";
				for(String b:a2)
				{
					mm2+=b;
				}
				obj.setKnowledge(mm2);
				obj.setDifficulty(rs.getInt("out_difficulty"));
				obj.setClaCount(rs.getInt("out_count"));
				list.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public List<SysOutline> showOutline(int terid,int graid,int arrageid,int subid){
		List<SysOutline> list = new ArrayList<SysOutline>();
		try{
			StringBuffer sql = new StringBuffer("select * from sys_outline ol,sys_course cou,Edu_Subject sb,edu_infobase ib ,edu_grade gr where cou.cou_id=ol.out_cou_id and cou.cou_term like '%暑假一%' "+
							"and cou.cou_subject=sb.SubjectName "+
							"and cou.cou_grade=gr.GradeName "+
							"and cou.cou_claLevel=ib.InfoName "+
							"and gr.GradeId= "+graid+
							"and sb.sub_id= "+subid+
							"and ib.InfoCode="+arrageid);
			System.out.println(sql);
			exec(sql);
			while(rs.next()){
				SysOutline obj=new SysOutline();
				String temp=rs.getString("out_claContent");
				String a[]=temp.split("\\.");
				a[0]=a[0]+".";
				for(int i=1;i<a.length;i++)
				{
					if(a[i].charAt((a[i].length()-1))>47&&a[i].charAt((a[i].length()-1))<58)
					{
						String befor=a[i].substring(0,a[i].length()-1);
						String last=a[i].substring(a[i].length()-1);//得到数字
						String temp2="<br/>"+last+".";
						a[i]=befor+temp2;
					}
				}
				String mm="";
				for(String b:a)
				{
					mm+=b;
				}
				obj.setClaContent(mm);
				String temp1=rs.getString("out_teaAims");
				String a1[]=temp.split("\\.");
				a1[0]=a1[0]+".";
				for(int i=1;i<a1.length;i++)
				{
					if(a1[i].charAt((a1[i].length()-1))>47&&a1[i].charAt((a1[i].length()-1))<58)
					{
						String befor=a1[i].substring(0,a1[i].length()-1);
						String last=a1[i].substring(a1[i].length()-1);//得到数字
						String temp2="<br/>"+last+".";
						a1[i]=befor+temp2;
					}
				}
				String mm1="";
				for(String b:a1)
				{
					mm1+=b;
				}
				obj.setTeaAims(mm1);
				obj.setKnowledge(rs.getString("out_knowledge"));
				obj.setDifficulty(rs.getInt("out_difficulty"));
//				obj.setClaCount(rs.getInt("out_count"));
				list.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public SysOutline showOutline(int couId,int calNum)
	{
		SysOutline obj = new SysOutline();
		try{
			StringBuffer sql = new StringBuffer("select * from sys_outline where out_cou_id="+couId+" and out_count="+calNum);
			exec(sql);
			while(rs.next()){
//				SysOutline obj=new SysOutline();
				obj.setClaContent(rs.getString("out_claContent"));
				obj.setTeaAims(rs.getString("out_teaAims"));
				obj.setKnowledge(rs.getString("out_knowledge"));
				obj.setDifficulty(rs.getInt("out_difficulty"));
				obj.setClaCount(rs.getInt("out_count"));
//				list.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return obj;
	}
	/**
	 * 查课次
	 * @return
	 */
	public int searchclaCount(int couId){
		int num = 0 ;
		try{
			StringBuffer sql = new StringBuffer("select COUNT(out_count) from sys_outline where out_cou_id="+couId);
			exec(sql);
			while(rs.next()){
				num = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return num;
	}
	/**
	 * show all school
	 * @return 
	 */
	public SysCourse findCourse(int id){
		SysCourse obj = null;
		try{
			StringBuffer sql = new StringBuffer("select * from sys_course where cou_id="+id);
			exec(sql);
			while(rs.next()){
				obj = new SysCourse();
				obj.setCouId(rs.getInt("cou_id"));
				obj.setYear(rs.getString("cou_year"));
				obj.setTerName(rs.getString("cou_term"));
				obj.setSubName(rs.getString("cou_subject"));
				obj.setGraName(rs.getString("cou_grade"));
				obj.setClaNumber(rs.getInt("cou_classNumber"));
				obj.setClaLevel(rs.getString("cou_claLevel"));
				obj.setClaType(rs.getString("cou_claType"));
				obj.setBookFee(rs.getDouble("cou_bookFee"));
				obj.setBookVersion(rs.getString("cou_bookVersion"));
				obj.setPerunitFee(rs.getInt("cou_perunitFee"));
				obj.setPredictNum(rs.getInt("cou_predictNum"));
				obj.setBeginTime(rs.getString("cou_beginTime"));
				obj.setEndTime(rs.getString("cou_endTime"));
				obj.setClaHour(rs.getDouble("cou_classHour"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return obj;
	}
	/**
	 * show all term
	 * @return
	 */
//	public List<BasTerm> searchTerm(){
//		List<BasTerm> list = new ArrayList<BasTerm>();
//		try{
//			StringBuffer sql = new StringBuffer("select * from Edu_Period where PeriodId>32 ");
//			exec(sql);
//			while(rs.next()){
//				BasTerm obj = new BasTerm();
//				obj.setTerId(rs.getInt("PeriodId"));
//				obj.setTerName(rs.getString("PeriodName"));
//				obj.setPeriodYear(rs.getString("PeriodYear"));
//				list.add(obj);
//			}
//		}catch(Exception e){
//			e.printStackTrace();
//		}finally{
//			this.closeAll(conn, pstmt, rs);
//		}
//		return list;
//	}
	
	/**
	 * @return
	 */
	public List<BasGrade> searchGrade(){
		List<BasGrade> list =new ArrayList<BasGrade>();
		try{
			StringBuffer sql = new StringBuffer("select * from Edu_Grade");
			exec(sql);
			while(rs.next()){
				BasGrade obj=new BasGrade();
				obj.setGraId(rs.getInt("GradeId"));
				obj.setGraName(rs.getString("GradeName"));
				list.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * show all subject
	 * @return 
	 */
//	public List<BasSubject> searchSubject(){
//		List<BasSubject> list = new ArrayList<BasSubject>();
//		try{
//			StringBuffer sql=new StringBuffer("select * from Edu_Subject order by SubjectId");
//			exec(sql);
//			while(rs.next()){
//				BasSubject obj=new BasSubject();
//				obj.setSubId(rs.getInt("SubjectId"));
//				obj.setSubName(rs.getString("SubjectName"));
//				list.add(obj);
//			}
//		}catch(Exception e){
//			e.printStackTrace();
//		}finally{
//			this.closeAll(conn, pstmt, rs);
//		}
//		return list;
//	}
	/**
	 * JAVA自带的函数 
	 * @param str
	 * @return
	 */
//	public boolean isNumeric(String str){
//		for (int i = str.length();--i>=0;){
//			if (!Character.isDigit(str.charAt(i))){
//				return false;
//			}
//		}
//		return true;
//	}
	/**
	 * 正则表达式
	 * @param str
	 * @return
	 */
//	public boolean isNumerics(String str){
//		if(str.matches("\\d*")){
//			return true;
//		}else{
//			return false;
//		}
//	}
}
